﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace AppTrans
{
    class FuelProjectsObject
    {
        public const string sql = @"
SELECT 'Закуплено топливо на склад:' as Итоги, '' as Топливо, '' as Проект, '' as Литры 
UNION ALL
SELECT 	'' as Итоги,
		DictionaryCategories.Name as Топливо,
		DictionaryProject.Name as Проект,
		convert(nchar(20),sum(t.Liters)) as Литры
FROM Transactions as t
LEFT JOIN DictionaryCategories on t.Category = DictionaryCategories.ID
LEFT JOIN DictionaryProject on t.Project = DictionaryProject.ID
WHERE t.Category in (select id from DictionaryCategories where Fuel = 1)
GROUP BY DictionaryCategories.Name, DictionaryProject.Name

UNION ALL
SELECT 'Всего заправлено в машины:' as Итоги, '' as Топливо, '' as Проект, '' as Литры 
UNION ALL

SELECT 	'' as Итоги,
		DictionaryCategories.Name as Топливо,
		DictionaryProject.Name as Проект,
		convert(nchar(20),sum(r.Liters)) as Литры
FROM Refueling as r
LEFT JOIN DictionaryCategories on r.Fuel = DictionaryCategories.ID
LEFT JOIN DictionaryProject on r.Project = DictionaryProject.ID
WHERE r.FuelOperType = 1
GROUP BY DictionaryCategories.Name, DictionaryProject.Name

UNION ALL
SELECT 'Остаток топлива:' as Итоги, '' as Топливо, '' as Проект, '' as Литры 
UNION ALL
SELECT '' as Итоги, TOTAL.Топливо as Топливо, TOTAL.Проект as Проект, convert(nchar(20),sum(TOTAL.Liters)) as Литры
FROM
(SELECT '' as Итоги,
		DictionaryCategories.Name as Топливо,
		DictionaryProject.Name as Проект,
		sum(t.Liters) as Liters
FROM Transactions as t
LEFT JOIN DictionaryCategories on t.Category = DictionaryCategories.ID
LEFT JOIN DictionaryProject on t.Project = DictionaryProject.ID
WHERE t.Category in (select id from DictionaryCategories where Fuel = 1)
GROUP BY DictionaryCategories.Name, DictionaryProject.Name
union all
SELECT 	'' as Итоги,
		DictionaryCategories.Name as Топливо,
		DictionaryProject.Name as Проект,
		-sum(r.Liters) as Liters
FROM Refueling as r
LEFT JOIN DictionaryCategories on r.Fuel = DictionaryCategories.ID
LEFT JOIN DictionaryProject on r.Project = DictionaryProject.ID
WHERE r.FuelOperType = 1
GROUP BY DictionaryCategories.Name, DictionaryProject.Name) as TOTAL
GROUP BY TOTAL.Топливо, TOTAL.Проект
";
        public string Итоги { get; set; }
        public string Топливо { get; set; }
        public string Проект { get; set; }
        public string Литры { get; set; }
    }
}
